<%@ page language="java" pageEncoding="UTF-8" %>
<%@ page import="com.ncr.teradata.bireport.*" %>
<%@ page import="java.util.List,java.util.ArrayList" %>
<%@ page import="java.math.BigDecimal" %>
<%@ page import="java.util.Enumeration" %>
<%@ page import="com.telecom.cms.dto.SysUser" %>
<%@ page import="com.telecom.cms.dao.*,java.sql.*" %>
<%
	String cityCode = "";
	String countryCode = "";
	
	String campId = "";
	String campSeqId = "";
	String minDateId = "";
	String maxDateId = "";
	String userId = "";
	String Camp_Cont_Rslt_Lvl1_Id = "";
	Enumeration e = request.getParameterNames();
	while (e.hasMoreElements())
	{
		String hdName = (String)e.nextElement();
		if (hdName.equals("campId"))
			campId = request.getParameter(hdName);
		if (hdName.equals("campSeqId"))
			campSeqId = request.getParameter(hdName);
		if (hdName.equals("minDateId"))
			minDateId = request.getParameter(hdName);
		if (hdName.equals("maxDateId"))
			maxDateId = request.getParameter(hdName);
		if (hdName.equals("userId"))
			userId = request.getParameter(hdName);
		if (hdName.equals("Camp_Cont_Rslt_Lvl1_Id"))
			Camp_Cont_Rslt_Lvl1_Id = request.getParameter(hdName);
	}
	
	String sql = "SELECT                                                                                                                                           "
				+ " 	COALESCE(T2.Camp_Name,T1.Camp_Id)                                                                                                                  "
				+ " 	,COALESCE(T4.User_Name,T1.User_Id)                                                                                                                 "
				+ " 	,CAST(Cont_Time AS VARCHAR(20))                                                                                                                    "
				+ " 	,COALESCE(T5.Code_Name_Lvl1,'')                                                                                                                    "
				+ " 	,T1.Cust_Id                                                                                                                                        "
				+ " 	,T1.Prd_Inst_Id                                                                                                                                    "
				+ " 	,COALESCE(T7.Accs_Nbr,'')                                                                                                                          "
				+ " 	,COALESCE(T7.User_Name,'')                                                                                                                         "
				+ " 	,COALESCE(T6.Std_Serv_Order_Stat_Name,'')                                                                                                          "
				+ " 	,COALESCE(CAST(CAST(T7.Serv_Order_Update_Date AS DATE FORMAT 'YYYY-MM-DD') AS VARCHAR(20)),'')                                                     "
				+ " 	,COALESCE(T8.Cust_Inter_Channel_Name,'')                                                                                                    "
				+ " FROM		(SELECT * FROM PD_MARTMETA_CAMP.CAMP_CONT_RSLT                                                                                               "
				+ " 			 QUALIFY ROW_NUMBER() OVER(PARTITION BY Camp_Id,Camp_Seq_Id,Cust_Id,Prd_Inst_Id ORDER BY Cont_Time DESC) = 1) T1                               "
				+ " LEFT JOIN 	PD_MARTMETA_CAMP.CAMP_TARGET_FINISH_TRACE  T7                                                                                            "
				+ " ON			T1.Camp_Id = T7.Camp_Id                                                                                                                      "
				+ " AND			T1.Camp_Seq_Id = T7.Camp_Seq_Id                                                                                                              "
				+ " AND			T1.Cust_Id = T7.Cust_Id                                                                                                                      "
				+ " AND			T1.Prd_Inst_Id = T7.Prd_Inst_Id                                                                                                              "
				+ " AND			T7.Serv_Order_Stat_Id IN ('10','11')                                                                                                         "
				+ " LEFT JOIN 	PV_DVIEW_Z0.MKT_CUST_INTER_CHANNEL  T8                                                                                               "
				+ " ON 			T7.Cust_Inter_Channel_Id = T8.Cust_Inter_Channel_Id                                                                                 "
				+ " LEFT JOIN	PD_MARTMETA_CAMP.CAMP_CAMP T2                                                                                                              "
				+ " ON			T1.Camp_Id = T2.Camp_Id                                                                                                                      "
				+ " LEFT JOIN	PD_MARTMETA_CAMP.SYS_USER T4                                                                                                               "
				+ " ON			T1.User_Id = CAST(T4.User_Id AS VARCHAR(20))                                                                                                 "
				+ " LEFT JOIN	( 	SELECT T51.Code_Id,T51.Code_Name,T52.Code_Id Code_Id_Lvl1,T52.Code_Name Code_Name_Lvl1                                                 "
				+ " 				FROM PD_MARTMETA_CAMP.SYS_CODE T51 JOIN PD_MARTMETA_CAMP.SYS_CODE T52 ON T51.Par_Code_Id = T52.Code_Id AND T51.Code_Type = T52.Code_Type) T5 "
				+ " ON			CAST(T1.Camp_Cont_Rslt_Lvl_Id AS VARCHAR(20)) = T5.Code_Id                                                                                   "
				+ " LEFT JOIN	PV_DVIEW_Z.STD_SERV_ORDER_STAT  T6                                                                                                         "
				+ " ON 			T7.Serv_Order_Stat_Id = T6.Std_Serv_Order_Stat_Cd                                                                                            "
				+ " WHERE	T1.User_Id like '%%' ";
	if (!campId.equals(""))
		sql += " AND 	T1.Camp_Id = " + campId + " ";
	if (!campSeqId.equals(""))
		sql += " AND 	T1.Camp_Seq_Id = " + campSeqId + " ";
	if (!minDateId.equals("") && !maxDateId.equals(""))
		sql += " AND	CAST(CAST(Cont_Time AS DATE FORMAT 'YYYYMMDD') AS CHAR(8)) BETWEEN '" + minDateId + "' AND '" + maxDateId + "' ";
	if (!userId.equals(""))
		sql += " AND 	T1.User_Id = " + userId + " ";
	if (!Camp_Cont_Rslt_Lvl1_Id.equals(""))
		sql += " AND 	T5.Code_Id_Lvl1 = " + Camp_Cont_Rslt_Lvl1_Id + " ";
	sql += " ORDER BY 3 DESC ";
	
	List datas = new ArrayList(); 
	try{
		Dao dao = new DaoImp();
		ResultSet rs = dao.select(sql);
		while (rs.next()){
			Object[] row = new Object[11];
			row[0] = rs.getObject(1);
			row[1] = rs.getObject(2);
			row[2] = rs.getObject(3);
			row[3] = rs.getObject(4);
			row[4] = rs.getObject(5);
			row[5] = rs.getObject(6);
			row[6] = rs.getObject(7);
			row[7] = rs.getObject(8);
			row[8] = rs.getObject(9);
			row[9] = rs.getObject(10);
			row[10] = rs.getObject(11);
			datas.add(row);
		}
		rs.close();
		dao.closeconn();
	}
	catch(Exception ex){
		ex.printStackTrace();
	}                                                                                                                                                                                                                                                                                                                                                            
%>

<html>
<head>
	<title>接触客户详细信息</title>
	<link href="../css/style.css" rel="stylesheet" type="text/css"/>
    <link href="../css/font.css" rel="stylesheet" type="text/css">
<SCRIPT>
	function exportData(spanId,fileName)
	{
		document.all.exportFileName.value = fileName+".xls";
	    exportExcel(document.getElementById(spanId).innerHTML,'../export');
		document.all.exportFileName.value=fileName;
	}
	
	function exportExcel(s,URL)
	{	
	    var oldAction=document.forms[0].action; 
		document.forms[0].exportContent.value="<META content=text/html; charset=GBK http-equiv=Content-Type/>"+s;
		document.forms[0].action=URL;
		document.forms[0].submit();	
		document.forms[0].action = oldAction;
	}
</SCRIPT>
</head>
<body>
<form name="form1" method="post" action="camp_audit_cont_detail.jsp">
	<input type="hidden" name="exportFileName" value="" />
	<input type="hidden" name="exportContent"  value="" />
	<table width="98%" height="100%"  border="0" align="center" cellpadding="0" cellspacing="1" >
		<tr>
			<td valign='top' align='left' bgcolor="#FFFFFF"> 
				<table border=0 width="100%" class="tablestyle4" style='table-layout:' cellspacing='2' cellpadding='2' bordercolordark='#ffffff' bordercolorlight='#ffffff'>
					<tr class="fontblue" width="100%" align="left">
						<td><img src="../images/title_left.gif" width="10" height="10"></td>
						<td align='left' valign='center'>
                    		接触客户详细信息：
                    	</td>
                        <td align="right" valign='center' width='80%'>
                    		<img name="btnSubmit" src="../images/button_export.gif" width="63" height="21" onclick="exportData('spanId1','Export data');" class="box_button">
                    	</td>
					</tr>
				</table>
				<span id="spanId1">
				<table border=1 width="100%" class="tablestyle1" style='table-layout:' cellspacing='0' cellpadding='0' bordercolordark='#ccffff' bordercolorlight='#9999cc'>
					<tr align="center">
						<td>营销活动</td>
						<td>外呼人员</td>
						<td>接触时间</td>
						<td>接触结果</td>
						<td>客户ID</td>
						<td>用户ID</td>
						<td>接入号码</td>
						<td>客户名称</td>
						<td>订单状态</td>
						<td>订单时间</td>
						<td>受理渠道</td>
					</tr>
					<%
						for(int i = 0; i < datas.size(); i++) {
							Object[] row = (Object[])datas.get(i); 
					%>
					<tr align="right">
						<TD><%= row[0] %></TD>
						<TD><%= row[1] %></TD>
						<TD><%= row[2] %></TD>
						<TD><%= row[3] %></TD>
						<TD><%= row[4] %>&nbsp;</TD>
						<TD><%= row[5] %>&nbsp;</TD>
						<TD><%= row[6] %>&nbsp;</TD>
						<TD><%= row[7] %>&nbsp;</TD>
						<TD><%= row[8] %>&nbsp;</TD>
						<TD><%= row[9] %>&nbsp;</TD>
						<TD><%= row[10] %>&nbsp;</TD>
					</tr>
					<% } %>
				</table>
				</span>
			</td>
		</tr>
	</table>
</form>
</body>
</html>